--1
SELECT	
REGION,PRODUCT,FEATURE, SUM(ALTCHNL_IN) ALTCHNL_IN, SUM(ALTCHNL_PCT) ALTCHNL_PCT,
		SUM(ALTCHNL_OUT) ALTCHNL_OUT, SUM(COR_IN) COR_IN, SUM(COR_PCT) COR_PCT,
		SUM(COR_OUT) COR_OUT, SUM(CS_IN) CS_IN, SUM(CS_PCT) CS_PCT, SUM(CS_OUT) CS_OUT,
		SUM(D2D_IN) D2D_IN, SUM(D2D_PCT) D2D_PCT, SUM(D2D_OUT) D2D_OUT,
		SUM(MOBILITYDMD_IN) MOBILITYDMD_IN, SUM(MOBILITYDMD_PCT) MOBILITYDMD_PCT,
		SUM(MOBILITYDMD_OUT) MOBILITYDMD_OUT, SUM(NATIONALRET_IN) NATIONALRET_IN,
		SUM(NATIONALRET_PCT) NATIONALRET_PCT, SUM(NATIONALRET_OUT) NATIONALRET_OUT,
		SUM(OBTM_IN) OBTM_IN, SUM(OBTM_PCT) OBTM_PCT, SUM(OBTM_OUT) OBTM_OUT,
		SUM(ONLINE_IN) ONLINE_IN, SUM(ONLINE_PCT) ONLINE_PCT, SUM(ONLINE_OUT) ONLINE_OUT,
		SUM(P2RCALLCEN_IN) P2RCALLCEN_IN, SUM(P2RCALLCEN_PCT) P2RCALLCEN_PCT,
		SUM(P2RCALLCEN_OUT) P2RCALLCEN_OUT, SUM(STORES_IN) STORES_IN,
		SUM(STORES_PCT) STORES_PCT, SUM(STORES_OUT) STORES_OUT, SUM(TE_IN) TE_IN,
		SUM(TE_PCT) TE_PCT, SUM(TE_OUT) TE_OUT, SUM(WEB_IN) WEB_IN, SUM(WEB_PCT) WEB_PCT,
		SUM(WEB_OUT) WEB_OUT, SUM(TOT) TOT, MAX(ROLLUP_TYPE) ROLLUP_TYPE
 
FROM	
(SELECT  DISTINCT Z.REGION, Z.PRODUCT,
CASE Z.FEATURE
 WHEN 'Expres' THEN 'Express'
 WHEN 'Hsia18' THEN 'Max18'
 WHEN 'BOT100' THEN 'VOIP 1000'
 WHEN 'BOT250' THEN 'VOIP 250'
 WHEN 'UNLMTD' THEN 'VOIP Unlimited'
 WHEN 'UFAM' THEN 'UFamily'
 WHEN 'U200la' THEN 'U200 Latino'
 WHEN 'Ubasic' THEN 'UBasic'
 WHEN 'Ucomp' THEN 'UComp'
 WHEN 'Udemo' THEN 'UDemo'
 ELSE Z.FEATURE
END FEATURE,
COALESCE(Y.ALTCHNL_I,0) ALTCHNL_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ALTCHNL_I/TOT_I AS DECIMAL(5,1))/10 END ALTCHNL_PCT, COALESCE(Y.ALTCHNL_O,0) ALTCHNL_OUT,
COALESCE(Y.COR_I,0) COR_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*COR_I/TOT_I AS DECIMAL(5,1))/10 END COR_PCT, COALESCE(Y.COR_O,0) COR_OUT,
COALESCE(Y.CS_I,0) CS_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*CS_I/TOT_I AS DECIMAL(5,1))/10 END CS_PCT, COALESCE(Y.CS_O,0) CS_OUT,
COALESCE(Y.D2D_I,0) D2D_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*D2D_I/TOT_I AS DECIMAL(5,1))/10 END D2D_PCT, COALESCE(Y.D2D_O,0) D2D_OUT,
COALESCE(Y.MOBILITYDMD_I,0) MOBILITYDMD_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*MOBILITYDMD_I/TOT_I AS DECIMAL(5,1))/10 END MOBILITYDMD_PCT, COALESCE(Y.MOBILITYDMD_O,0) MOBILITYDMD_OUT,
COALESCE(Y.NATIONALRET_I,0) NATIONALRET_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*NATIONALRET_I/TOT_I AS DECIMAL(5,1))/10 END NATIONALRET_PCT, COALESCE(Y.NATIONALRET_O,0) NATIONALRET_OUT,
COALESCE(Y.OBTM_I,0) OBTM_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*OBTM_I/TOT_I AS DECIMAL(5,1))/10 END OBTM_PCT, COALESCE(Y.OBTM_O,0) OBTM_OUT,
COALESCE(Y.ONLINE_I,0) ONLINE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ONLINE_I/TOT_I AS DECIMAL(5,1))/10 END ONLINE_PCT, COALESCE(Y.ONLINE_O,0) ONLINE_OUT,
COALESCE(Y.P2RCALLCEN_I,0) P2RCALLCEN_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*P2RCALLCEN_I/TOT_I AS DECIMAL(5,1))/10 END P2RCALLCEN_PCT, COALESCE(Y.P2RCALLCEN_O,0) P2RCALLCEN_OUT,
COALESCE(Y.STORES_I,0) STORES_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*STORES_I/TOT_I AS DECIMAL(5,1))/10 END STORES_PCT, COALESCE(Y.STORES_O,0) STORES_OUT,
COALESCE(Y.TE_I,0) TE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*TE_I/TOT_I AS DECIMAL(5,1))/10 END TE_PCT, COALESCE(Y.TE_O,0) TE_OUT,
COALESCE(Y.WEB_I,0) WEB_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*WEB_I/TOT_I AS DECIMAL(5,1))/10 END WEB_PCT, COALESCE(Y.WEB_O,0) WEB_OUT,
COALESCE(TOT_I,0)  + COALESCE(TOT_O,0)  TOT,
'REGION' ROLLUP_TYPE
FROM (
SELECT B.REGION, A.PRODUCT, A.FEATURE 
FROM (SELECT PRODUCT, FEATURE FROM MIS.ACC_RAW WHERE FEATURE NOT IN ('000','BVSTAN') GROUP BY 1,2) A,
     (SELECT AREA_MGR, REGION FROM MIS.ACC_BASE_DATA WHERE REGION GT '' GROUP BY 1,2) B) Z
LEFT OUTER JOIN (
SELECT B.REGION, A.PRODUCT, A.FEATURE,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_O,
 TOT_I-(ALTCHNL_I+COR_I+CS_I+D2D_I+MOBILITYDMD_I+NATIONALRET_I+OBTM_I+ONLINE_I+P2RCALLCEN_I+STORES_I+TE_I+WEB_I) OTH_I,
 TOT_O-(ALTCHNL_O+COR_O+CS_O+D2D_O+MOBILITYDMD_O+NATIONALRET_O+OBTM_O+ONLINE_O+P2RCALLCEN_O+STORES_O+TE_O+WEB_O) OTH_O,
 SUM(CASE WHEN A.DIRECTION='IN' THEN 1 ELSE 0 END) TOT_I,
 SUM(CASE WHEN A.DIRECTION='OUT' THEN 1 ELSE 0 END) TOT_O
FROM MIS.ACC_RAW A
 INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID,
 (SELECT MIN(CALENDAR_DATE) BOM, MAX(CALENDAR_DATE) EOM
  FROM SYS_CALENDAR.CALENDAR
  WHERE MONTH_OF_CALENDAR=(
  SELECT MONTH_OF_CALENDAR  FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)) C
WHERE A.FEATURE NOT IN ('000','BVSTAN')
AND A.START_DT BETWEEN C.BOM AND C.EOM
GROUP BY 1,2,3) Y ON Z.REGION=Y.REGION AND Z.PRODUCT=Y.PRODUCT AND Z.FEATURE=Y.FEATURE
) sums
GROUP	BY 1, ROLLUP (2,3)
ORDER	BY 1,2,3,4;
--2
SELECT	
MARKET,PRODUCT,FEATURE, SUM(ALTCHNL_IN) ALTCHNL_IN, SUM(ALTCHNL_PCT) ALTCHNL_PCT,
		SUM(ALTCHNL_OUT) ALTCHNL_OUT, SUM(COR_IN) COR_IN, SUM(COR_PCT) COR_PCT,
		SUM(COR_OUT) COR_OUT, SUM(CS_IN) CS_IN, SUM(CS_PCT) CS_PCT, SUM(CS_OUT) CS_OUT,
		SUM(D2D_IN) D2D_IN, SUM(D2D_PCT) D2D_PCT, SUM(D2D_OUT) D2D_OUT,
		SUM(MOBILITYDMD_IN) MOBILITYDMD_IN, SUM(MOBILITYDMD_PCT) MOBILITYDMD_PCT,
		SUM(MOBILITYDMD_OUT) MOBILITYDMD_OUT, SUM(NATIONALRET_IN) NATIONALRET_IN,
		SUM(NATIONALRET_PCT) NATIONALRET_PCT, SUM(NATIONALRET_OUT) NATIONALRET_OUT,
		SUM(OBTM_IN) OBTM_IN, SUM(OBTM_PCT) OBTM_PCT, SUM(OBTM_OUT) OBTM_OUT,
		SUM(ONLINE_IN) ONLINE_IN, SUM(ONLINE_PCT) ONLINE_PCT, SUM(ONLINE_OUT) ONLINE_OUT,
		SUM(P2RCALLCEN_IN) P2RCALLCEN_IN, SUM(P2RCALLCEN_PCT) P2RCALLCEN_PCT,
		SUM(P2RCALLCEN_OUT) P2RCALLCEN_OUT, SUM(STORES_IN) STORES_IN,
		SUM(STORES_PCT) STORES_PCT, SUM(STORES_OUT) STORES_OUT, SUM(TE_IN) TE_IN,
		SUM(TE_PCT) TE_PCT, SUM(TE_OUT) TE_OUT, SUM(WEB_IN) WEB_IN, SUM(WEB_PCT) WEB_PCT,
		SUM(WEB_OUT) WEB_OUT, SUM(TOT) TOT, MAX(ROLLUP_TYPE) ROLLUP_TYPE
 
FROM	
(SELECT  DISTINCT Z.MARKET, Z.PRODUCT,
CASE Z.FEATURE
 WHEN 'Expres' THEN 'Express'
 WHEN 'Hsia18' THEN 'Max18'
 WHEN 'BOT100' THEN 'VOIP 1000'
 WHEN 'BOT250' THEN 'VOIP 250'
 WHEN 'UNLMTD' THEN 'VOIP Unlimited'
 WHEN 'UFAM' THEN 'UFamily'
 WHEN 'U200la' THEN 'U200 Latino'
 WHEN 'Ubasic' THEN 'UBasic'
 WHEN 'Ucomp' THEN 'UComp'
 WHEN 'Udemo' THEN 'UDemo'
 ELSE Z.FEATURE
END FEATURE,
COALESCE(Y.ALTCHNL_I,0) ALTCHNL_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ALTCHNL_I/TOT_I AS DECIMAL(5,1))/10 END ALTCHNL_PCT, COALESCE(Y.ALTCHNL_O,0) ALTCHNL_OUT,
COALESCE(Y.COR_I,0) COR_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*COR_I/TOT_I AS DECIMAL(5,1))/10 END COR_PCT, COALESCE(Y.COR_O,0) COR_OUT,
COALESCE(Y.CS_I,0) CS_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*CS_I/TOT_I AS DECIMAL(5,1))/10 END CS_PCT, COALESCE(Y.CS_O,0) CS_OUT,
COALESCE(Y.D2D_I,0) D2D_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*D2D_I/TOT_I AS DECIMAL(5,1))/10 END D2D_PCT, COALESCE(Y.D2D_O,0) D2D_OUT,
COALESCE(Y.MOBILITYDMD_I,0) MOBILITYDMD_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*MOBILITYDMD_I/TOT_I AS DECIMAL(5,1))/10 END MOBILITYDMD_PCT, COALESCE(Y.MOBILITYDMD_O,0) MOBILITYDMD_OUT,
COALESCE(Y.NATIONALRET_I,0) NATIONALRET_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*NATIONALRET_I/TOT_I AS DECIMAL(5,1))/10 END NATIONALRET_PCT, COALESCE(Y.NATIONALRET_O,0) NATIONALRET_OUT,
COALESCE(Y.OBTM_I,0) OBTM_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*OBTM_I/TOT_I AS DECIMAL(5,1))/10 END OBTM_PCT, COALESCE(Y.OBTM_O,0) OBTM_OUT,
COALESCE(Y.ONLINE_I,0) ONLINE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ONLINE_I/TOT_I AS DECIMAL(5,1))/10 END ONLINE_PCT, COALESCE(Y.ONLINE_O,0) ONLINE_OUT,
COALESCE(Y.P2RCALLCEN_I,0) P2RCALLCEN_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*P2RCALLCEN_I/TOT_I AS DECIMAL(5,1))/10 END P2RCALLCEN_PCT, COALESCE(Y.P2RCALLCEN_O,0) P2RCALLCEN_OUT,
COALESCE(Y.STORES_I,0) STORES_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*STORES_I/TOT_I AS DECIMAL(5,1))/10 END STORES_PCT, COALESCE(Y.STORES_O,0) STORES_OUT,
COALESCE(Y.TE_I,0) TE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*TE_I/TOT_I AS DECIMAL(5,1))/10 END TE_PCT, COALESCE(Y.TE_O,0) TE_OUT,
COALESCE(Y.WEB_I,0) WEB_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*WEB_I/TOT_I AS DECIMAL(5,1))/10 END WEB_PCT, COALESCE(Y.WEB_O,0) WEB_OUT,
COALESCE(TOT_I,0)  + COALESCE(TOT_O,0)  TOT,
'MARKET' ROLLUP_TYPE
FROM (
SELECT B.MARKET, A.PRODUCT, A.FEATURE 
FROM (SELECT PRODUCT, FEATURE FROM MIS.ACC_RAW WHERE FEATURE NOT IN ('000','BVSTAN') GROUP BY 1,2) A,
     (SELECT AREA_MGR,  MARKET FROM MIS.ACC_BASE_DATA WHERE TRAC_ID GT MARKET GROUP BY 1,2) B) Z
LEFT OUTER JOIN (
SELECT B.MARKET, A.PRODUCT, A.FEATURE,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_O,
 TOT_I-(ALTCHNL_I+COR_I+CS_I+D2D_I+MOBILITYDMD_I+NATIONALRET_I+OBTM_I+ONLINE_I+P2RCALLCEN_I+STORES_I+TE_I+WEB_I) OTH_I,
 TOT_O-(ALTCHNL_O+COR_O+CS_O+D2D_O+MOBILITYDMD_O+NATIONALRET_O+OBTM_O+ONLINE_O+P2RCALLCEN_O+STORES_O+TE_O+WEB_O) OTH_O,
 SUM(CASE WHEN A.DIRECTION='IN' THEN 1 ELSE 0 END) TOT_I,
 SUM(CASE WHEN A.DIRECTION='OUT' THEN 1 ELSE 0 END) TOT_O
FROM MIS.ACC_RAW A
 INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID,
 (SELECT MIN(CALENDAR_DATE) BOM, MAX(CALENDAR_DATE) EOM
  FROM SYS_CALENDAR.CALENDAR
  WHERE MONTH_OF_CALENDAR=(
  SELECT MONTH_OF_CALENDAR  FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)) C
WHERE A.FEATURE NOT IN ('000','BVSTAN')
AND A.START_DT BETWEEN C.BOM AND C.EOM
GROUP BY 1,2,3) Y ON Z.MARKET=Y.MARKET AND Z.PRODUCT=Y.PRODUCT AND Z.FEATURE=Y.FEATURE
) sums
GROUP	BY 1, ROLLUP (2,3)
ORDER	BY 1,2,3,4;
--3
SELECT	
STATE_CD,PRODUCT,FEATURE, SUM(ALTCHNL_IN) ALTCHNL_IN, SUM(ALTCHNL_PCT) ALTCHNL_PCT,
		SUM(ALTCHNL_OUT) ALTCHNL_OUT, SUM(COR_IN) COR_IN, SUM(COR_PCT) COR_PCT,
		SUM(COR_OUT) COR_OUT, SUM(CS_IN) CS_IN, SUM(CS_PCT) CS_PCT, SUM(CS_OUT) CS_OUT,
		SUM(D2D_IN) D2D_IN, SUM(D2D_PCT) D2D_PCT, SUM(D2D_OUT) D2D_OUT,
		SUM(MOBILITYDMD_IN) MOBILITYDMD_IN, SUM(MOBILITYDMD_PCT) MOBILITYDMD_PCT,
		SUM(MOBILITYDMD_OUT) MOBILITYDMD_OUT, SUM(NATIONALRET_IN) NATIONALRET_IN,
		SUM(NATIONALRET_PCT) NATIONALRET_PCT, SUM(NATIONALRET_OUT) NATIONALRET_OUT,
		SUM(OBTM_IN) OBTM_IN, SUM(OBTM_PCT) OBTM_PCT, SUM(OBTM_OUT) OBTM_OUT,
		SUM(ONLINE_IN) ONLINE_IN, SUM(ONLINE_PCT) ONLINE_PCT, SUM(ONLINE_OUT) ONLINE_OUT,
		SUM(P2RCALLCEN_IN) P2RCALLCEN_IN, SUM(P2RCALLCEN_PCT) P2RCALLCEN_PCT,
		SUM(P2RCALLCEN_OUT) P2RCALLCEN_OUT, SUM(STORES_IN) STORES_IN,
		SUM(STORES_PCT) STORES_PCT, SUM(STORES_OUT) STORES_OUT, SUM(TE_IN) TE_IN,
		SUM(TE_PCT) TE_PCT, SUM(TE_OUT) TE_OUT, SUM(WEB_IN) WEB_IN, SUM(WEB_PCT) WEB_PCT,
		SUM(WEB_OUT) WEB_OUT, SUM(TOT) TOT, MAX(ROLLUP_TYPE) ROLLUP_TYPE
 
FROM	
(SELECT  DISTINCT Z.STATE_CD, Z.PRODUCT,
CASE Z.FEATURE
 WHEN 'Expres' THEN 'Express'
 WHEN 'Hsia18' THEN 'Max18'
 WHEN 'BOT100' THEN 'VOIP 1000'
 WHEN 'BOT250' THEN 'VOIP 250'
 WHEN 'UNLMTD' THEN 'VOIP Unlimited'
 WHEN 'UFAM' THEN 'UFamily'
 WHEN 'U200la' THEN 'U200 Latino'
 WHEN 'Ubasic' THEN 'UBasic'
 WHEN 'Ucomp' THEN 'UComp'
 WHEN 'Udemo' THEN 'UDemo'
 ELSE Z.FEATURE
END FEATURE,
COALESCE(Y.ALTCHNL_I,0) ALTCHNL_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ALTCHNL_I/TOT_I AS DECIMAL(5,1))/10 END ALTCHNL_PCT, COALESCE(Y.ALTCHNL_O,0) ALTCHNL_OUT,
COALESCE(Y.COR_I,0) COR_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*COR_I/TOT_I AS DECIMAL(5,1))/10 END COR_PCT, COALESCE(Y.COR_O,0) COR_OUT,
COALESCE(Y.CS_I,0) CS_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*CS_I/TOT_I AS DECIMAL(5,1))/10 END CS_PCT, COALESCE(Y.CS_O,0) CS_OUT,
COALESCE(Y.D2D_I,0) D2D_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*D2D_I/TOT_I AS DECIMAL(5,1))/10 END D2D_PCT, COALESCE(Y.D2D_O,0) D2D_OUT,
COALESCE(Y.MOBILITYDMD_I,0) MOBILITYDMD_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*MOBILITYDMD_I/TOT_I AS DECIMAL(5,1))/10 END MOBILITYDMD_PCT, COALESCE(Y.MOBILITYDMD_O,0) MOBILITYDMD_OUT,
COALESCE(Y.NATIONALRET_I,0) NATIONALRET_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*NATIONALRET_I/TOT_I AS DECIMAL(5,1))/10 END NATIONALRET_PCT, COALESCE(Y.NATIONALRET_O,0) NATIONALRET_OUT,
COALESCE(Y.OBTM_I,0) OBTM_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*OBTM_I/TOT_I AS DECIMAL(5,1))/10 END OBTM_PCT, COALESCE(Y.OBTM_O,0) OBTM_OUT,
COALESCE(Y.ONLINE_I,0) ONLINE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ONLINE_I/TOT_I AS DECIMAL(5,1))/10 END ONLINE_PCT, COALESCE(Y.ONLINE_O,0) ONLINE_OUT,
COALESCE(Y.P2RCALLCEN_I,0) P2RCALLCEN_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*P2RCALLCEN_I/TOT_I AS DECIMAL(5,1))/10 END P2RCALLCEN_PCT, COALESCE(Y.P2RCALLCEN_O,0) P2RCALLCEN_OUT,
COALESCE(Y.STORES_I,0) STORES_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*STORES_I/TOT_I AS DECIMAL(5,1))/10 END STORES_PCT, COALESCE(Y.STORES_O,0) STORES_OUT,
COALESCE(Y.TE_I,0) TE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*TE_I/TOT_I AS DECIMAL(5,1))/10 END TE_PCT, COALESCE(Y.TE_O,0) TE_OUT,
COALESCE(Y.WEB_I,0) WEB_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*WEB_I/TOT_I AS DECIMAL(5,1))/10 END WEB_PCT, COALESCE(Y.WEB_O,0) WEB_OUT,
COALESCE(TOT_I,0)  + COALESCE(TOT_O,0)  TOT,
'STATE_CD' ROLLUP_TYPE
FROM (
SELECT B.STATE_CD, A.PRODUCT, A.FEATURE 
FROM (SELECT PRODUCT, FEATURE FROM MIS.ACC_RAW WHERE FEATURE NOT IN ('000','BVSTAN') GROUP BY 1,2) A,
     (SELECT AREA_MGR, SUBSTR(TRAC_ID,1,2)  STATE_CD FROM MIS.ACC_BASE_DATA WHERE TRAC_ID GT '' GROUP BY 1,2) B) Z
LEFT OUTER JOIN (
SELECT SUBSTR(B.TRAC_ID,1,2)  STATE_CD, A.PRODUCT, A.FEATURE,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_O,
 TOT_I-(ALTCHNL_I+COR_I+CS_I+D2D_I+MOBILITYDMD_I+NATIONALRET_I+OBTM_I+ONLINE_I+P2RCALLCEN_I+STORES_I+TE_I+WEB_I) OTH_I,
 TOT_O-(ALTCHNL_O+COR_O+CS_O+D2D_O+MOBILITYDMD_O+NATIONALRET_O+OBTM_O+ONLINE_O+P2RCALLCEN_O+STORES_O+TE_O+WEB_O) OTH_O,
 SUM(CASE WHEN A.DIRECTION='IN' THEN 1 ELSE 0 END) TOT_I,
 SUM(CASE WHEN A.DIRECTION='OUT' THEN 1 ELSE 0 END) TOT_O
FROM MIS.ACC_RAW A
 INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID,
 (SELECT MIN(CALENDAR_DATE) BOM, MAX(CALENDAR_DATE) EOM
  FROM SYS_CALENDAR.CALENDAR
  WHERE MONTH_OF_CALENDAR=(
  SELECT MONTH_OF_CALENDAR  FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)) C
WHERE A.FEATURE NOT IN ('000','BVSTAN')
AND A.START_DT BETWEEN C.BOM AND C.EOM
GROUP BY 1,2,3) Y ON Z.STATE_CD=Y.STATE_CD AND Z.PRODUCT=Y.PRODUCT AND Z.FEATURE=Y.FEATURE
) sums
GROUP	BY 1, ROLLUP (2,3)
ORDER	BY 1,2,3,4;
--4
--4
SELECT	
AREA_MGR,PRODUCT,FEATURE, SUM(ALTCHNL_IN) ALTCHNL_IN, SUM(ALTCHNL_PCT) ALTCHNL_PCT,
		SUM(ALTCHNL_OUT) ALTCHNL_OUT, SUM(COR_IN) COR_IN, SUM(COR_PCT) COR_PCT,
		SUM(COR_OUT) COR_OUT, SUM(CS_IN) CS_IN, SUM(CS_PCT) CS_PCT, SUM(CS_OUT) CS_OUT,
		SUM(D2D_IN) D2D_IN, SUM(D2D_PCT) D2D_PCT, SUM(D2D_OUT) D2D_OUT,
		SUM(MOBILITYDMD_IN) MOBILITYDMD_IN, SUM(MOBILITYDMD_PCT) MOBILITYDMD_PCT,
		SUM(MOBILITYDMD_OUT) MOBILITYDMD_OUT, SUM(NATIONALRET_IN) NATIONALRET_IN,
		SUM(NATIONALRET_PCT) NATIONALRET_PCT, SUM(NATIONALRET_OUT) NATIONALRET_OUT,
		SUM(OBTM_IN) OBTM_IN, SUM(OBTM_PCT) OBTM_PCT, SUM(OBTM_OUT) OBTM_OUT,
		SUM(ONLINE_IN) ONLINE_IN, SUM(ONLINE_PCT) ONLINE_PCT, SUM(ONLINE_OUT) ONLINE_OUT,
		SUM(P2RCALLCEN_IN) P2RCALLCEN_IN, SUM(P2RCALLCEN_PCT) P2RCALLCEN_PCT,
		SUM(P2RCALLCEN_OUT) P2RCALLCEN_OUT, SUM(STORES_IN) STORES_IN,
		SUM(STORES_PCT) STORES_PCT, SUM(STORES_OUT) STORES_OUT, SUM(TE_IN) TE_IN,
		SUM(TE_PCT) TE_PCT, SUM(TE_OUT) TE_OUT, SUM(WEB_IN) WEB_IN, SUM(WEB_PCT) WEB_PCT,
		SUM(WEB_OUT) WEB_OUT, SUM(TOT) TOT, MAX(ROLLUP_TYPE) ROLLUP_TYPE
 
FROM	
(SELECT Z.AREA_MGR, Z.PRODUCT,
CASE Z.FEATURE
 WHEN 'Expres' THEN 'Express'
 WHEN 'Hsia18' THEN 'Max18'
 WHEN 'BOT100' THEN 'VOIP 1000'
 WHEN 'BOT250' THEN 'VOIP 250'
 WHEN 'UNLMTD' THEN 'VOIP Unlimited'
 WHEN 'UFAM' THEN 'UFamily'
 WHEN 'U200la' THEN 'U200 Latino'
 WHEN 'Ubasic' THEN 'UBasic'
 WHEN 'Ucomp' THEN 'UComp'
 WHEN 'Udemo' THEN 'UDemo'
 ELSE Z.FEATURE
END FEATURE,
COALESCE(Y.ALTCHNL_I,0) ALTCHNL_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ALTCHNL_I/TOT_I AS DECIMAL(5,1))/10 END ALTCHNL_PCT, COALESCE(Y.ALTCHNL_O,0) ALTCHNL_OUT,
COALESCE(Y.COR_I,0) COR_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*COR_I/TOT_I AS DECIMAL(5,1))/10 END COR_PCT, COALESCE(Y.COR_O,0) COR_OUT,
COALESCE(Y.CS_I,0) CS_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*CS_I/TOT_I AS DECIMAL(5,1))/10 END CS_PCT, COALESCE(Y.CS_O,0) CS_OUT,
COALESCE(Y.D2D_I,0) D2D_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*D2D_I/TOT_I AS DECIMAL(5,1))/10 END D2D_PCT, COALESCE(Y.D2D_O,0) D2D_OUT,
COALESCE(Y.MOBILITYDMD_I,0) MOBILITYDMD_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*MOBILITYDMD_I/TOT_I AS DECIMAL(5,1))/10 END MOBILITYDMD_PCT, COALESCE(Y.MOBILITYDMD_O,0) MOBILITYDMD_OUT,
COALESCE(Y.NATIONALRET_I,0) NATIONALRET_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*NATIONALRET_I/TOT_I AS DECIMAL(5,1))/10 END NATIONALRET_PCT, COALESCE(Y.NATIONALRET_O,0) NATIONALRET_OUT,
COALESCE(Y.OBTM_I,0) OBTM_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*OBTM_I/TOT_I AS DECIMAL(5,1))/10 END OBTM_PCT, COALESCE(Y.OBTM_O,0) OBTM_OUT,
COALESCE(Y.ONLINE_I,0) ONLINE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ONLINE_I/TOT_I AS DECIMAL(5,1))/10 END ONLINE_PCT, COALESCE(Y.ONLINE_O,0) ONLINE_OUT,
COALESCE(Y.P2RCALLCEN_I,0) P2RCALLCEN_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*P2RCALLCEN_I/TOT_I AS DECIMAL(5,1))/10 END P2RCALLCEN_PCT, COALESCE(Y.P2RCALLCEN_O,0) P2RCALLCEN_OUT,
COALESCE(Y.STORES_I,0) STORES_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*STORES_I/TOT_I AS DECIMAL(5,1))/10 END STORES_PCT, COALESCE(Y.STORES_O,0) STORES_OUT,
COALESCE(Y.TE_I,0) TE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*TE_I/TOT_I AS DECIMAL(5,1))/10 END TE_PCT, COALESCE(Y.TE_O,0) TE_OUT,
COALESCE(Y.WEB_I,0) WEB_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*WEB_I/TOT_I AS DECIMAL(5,1))/10 END WEB_PCT, COALESCE(Y.WEB_O,0) WEB_OUT,
COALESCE(TOT_I,0)  + COALESCE(TOT_O,0)  TOT,
'AREA_MGR' ROLLUP_TYPE
FROM (
SELECT B.AREA_MGR, A.PRODUCT, A.FEATURE 
FROM (SELECT PRODUCT, FEATURE FROM MIS.ACC_RAW WHERE FEATURE NOT IN ('000','BVSTAN') GROUP BY 1,2) A,
     (SELECT AREA_MGR FROM MIS.ACC_BASE_DATA WHERE AREA_MGR GT'' GROUP BY 1) B) Z
LEFT OUTER JOIN (
SELECT B.AREA_MGR, A.PRODUCT, A.FEATURE,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_O,
 TOT_I-(ALTCHNL_I+COR_I+CS_I+D2D_I+MOBILITYDMD_I+NATIONALRET_I+OBTM_I+ONLINE_I+P2RCALLCEN_I+STORES_I+TE_I+WEB_I) OTH_I,
 TOT_O-(ALTCHNL_O+COR_O+CS_O+D2D_O+MOBILITYDMD_O+NATIONALRET_O+OBTM_O+ONLINE_O+P2RCALLCEN_O+STORES_O+TE_O+WEB_O) OTH_O,
 SUM(CASE WHEN A.DIRECTION='IN' THEN 1 ELSE 0 END) TOT_I,
 SUM(CASE WHEN A.DIRECTION='OUT' THEN 1 ELSE 0 END) TOT_O
FROM MIS.ACC_RAW A
 INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID,
 (SELECT MIN(CALENDAR_DATE) BOM, MAX(CALENDAR_DATE) EOM
  FROM SYS_CALENDAR.CALENDAR
  WHERE MONTH_OF_CALENDAR=(
  SELECT MONTH_OF_CALENDAR  FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)) C
WHERE A.FEATURE NOT IN ('000','BVSTAN')
AND A.START_DT BETWEEN C.BOM AND C.EOM
GROUP BY 1,2,3) Y ON Z.AREA_MGR=Y.AREA_MGR AND Z.PRODUCT=Y.PRODUCT AND Z.FEATURE=Y.FEATURE
) sums
GROUP	BY 1, ROLLUP (2,3)
ORDER	BY 1,2,3,4;
;

--5
SELECT	
ACCT_MGR,PRODUCT,FEATURE, SUM(ALTCHNL_IN) ALTCHNL_IN, SUM(ALTCHNL_PCT) ALTCHNL_PCT,
		SUM(ALTCHNL_OUT) ALTCHNL_OUT, SUM(COR_IN) COR_IN, SUM(COR_PCT) COR_PCT,
		SUM(COR_OUT) COR_OUT, SUM(CS_IN) CS_IN, SUM(CS_PCT) CS_PCT, SUM(CS_OUT) CS_OUT,
		SUM(D2D_IN) D2D_IN, SUM(D2D_PCT) D2D_PCT, SUM(D2D_OUT) D2D_OUT,
		SUM(MOBILITYDMD_IN) MOBILITYDMD_IN, SUM(MOBILITYDMD_PCT) MOBILITYDMD_PCT,
		SUM(MOBILITYDMD_OUT) MOBILITYDMD_OUT, SUM(NATIONALRET_IN) NATIONALRET_IN,
		SUM(NATIONALRET_PCT) NATIONALRET_PCT, SUM(NATIONALRET_OUT) NATIONALRET_OUT,
		SUM(OBTM_IN) OBTM_IN, SUM(OBTM_PCT) OBTM_PCT, SUM(OBTM_OUT) OBTM_OUT,
		SUM(ONLINE_IN) ONLINE_IN, SUM(ONLINE_PCT) ONLINE_PCT, SUM(ONLINE_OUT) ONLINE_OUT,
		SUM(P2RCALLCEN_IN) P2RCALLCEN_IN, SUM(P2RCALLCEN_PCT) P2RCALLCEN_PCT,
		SUM(P2RCALLCEN_OUT) P2RCALLCEN_OUT, SUM(STORES_IN) STORES_IN,
		SUM(STORES_PCT) STORES_PCT, SUM(STORES_OUT) STORES_OUT, SUM(TE_IN) TE_IN,
		SUM(TE_PCT) TE_PCT, SUM(TE_OUT) TE_OUT, SUM(WEB_IN) WEB_IN, SUM(WEB_PCT) WEB_PCT,
		SUM(WEB_OUT) WEB_OUT, SUM(TOT) TOT, MAX(ROLLUP_TYPE) ROLLUP_TYPE
 
FROM	
(SELECT  Z.ACCT_MGR, Z.PRODUCT,
CASE Z.FEATURE
 WHEN 'Expres' THEN 'Express'
 WHEN 'Hsia18' THEN 'Max18'
 WHEN 'BOT100' THEN 'VOIP 1000'
 WHEN 'BOT250' THEN 'VOIP 250'
 WHEN 'UNLMTD' THEN 'VOIP Unlimited'
 WHEN 'UFAM' THEN 'UFamily'
 WHEN 'U200la' THEN 'U200 Latino'
 WHEN 'Ubasic' THEN 'UBasic'
 WHEN 'Ucomp' THEN 'UComp'
 WHEN 'Udemo' THEN 'UDemo'
 ELSE Z.FEATURE
END FEATURE,
COALESCE(Y.ALTCHNL_I,0) ALTCHNL_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ALTCHNL_I/TOT_I AS DECIMAL(5,1))/10 END ALTCHNL_PCT, COALESCE(Y.ALTCHNL_O,0) ALTCHNL_OUT,
COALESCE(Y.COR_I,0) COR_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*COR_I/TOT_I AS DECIMAL(5,1))/10 END COR_PCT, COALESCE(Y.COR_O,0) COR_OUT,
COALESCE(Y.CS_I,0) CS_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*CS_I/TOT_I AS DECIMAL(5,1))/10 END CS_PCT, COALESCE(Y.CS_O,0) CS_OUT,
COALESCE(Y.D2D_I,0) D2D_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*D2D_I/TOT_I AS DECIMAL(5,1))/10 END D2D_PCT, COALESCE(Y.D2D_O,0) D2D_OUT,
COALESCE(Y.MOBILITYDMD_I,0) MOBILITYDMD_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*MOBILITYDMD_I/TOT_I AS DECIMAL(5,1))/10 END MOBILITYDMD_PCT, COALESCE(Y.MOBILITYDMD_O,0) MOBILITYDMD_OUT,
COALESCE(Y.NATIONALRET_I,0) NATIONALRET_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*NATIONALRET_I/TOT_I AS DECIMAL(5,1))/10 END NATIONALRET_PCT, COALESCE(Y.NATIONALRET_O,0) NATIONALRET_OUT,
COALESCE(Y.OBTM_I,0) OBTM_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*OBTM_I/TOT_I AS DECIMAL(5,1))/10 END OBTM_PCT, COALESCE(Y.OBTM_O,0) OBTM_OUT,
COALESCE(Y.ONLINE_I,0) ONLINE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ONLINE_I/TOT_I AS DECIMAL(5,1))/10 END ONLINE_PCT, COALESCE(Y.ONLINE_O,0) ONLINE_OUT,
COALESCE(Y.P2RCALLCEN_I,0) P2RCALLCEN_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*P2RCALLCEN_I/TOT_I AS DECIMAL(5,1))/10 END P2RCALLCEN_PCT, COALESCE(Y.P2RCALLCEN_O,0) P2RCALLCEN_OUT,
COALESCE(Y.STORES_I,0) STORES_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*STORES_I/TOT_I AS DECIMAL(5,1))/10 END STORES_PCT, COALESCE(Y.STORES_O,0) STORES_OUT,
COALESCE(Y.TE_I,0) TE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*TE_I/TOT_I AS DECIMAL(5,1))/10 END TE_PCT, COALESCE(Y.TE_O,0) TE_OUT,
COALESCE(Y.WEB_I,0) WEB_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*WEB_I/TOT_I AS DECIMAL(5,1))/10 END WEB_PCT, COALESCE(Y.WEB_O,0) WEB_OUT,
COALESCE(TOT_I,0)  + COALESCE(TOT_O,0)  TOT,
'ACCT_MGR' ROLLUP_TYPE
FROM (
SELECT B.AREA_MGR, B.ACCT_MGR, A.PRODUCT, A.FEATURE 
FROM (SELECT PRODUCT, FEATURE FROM MIS.ACC_RAW WHERE FEATURE NOT IN ('000','BVSTAN') GROUP BY 1,2) A,
     (SELECT AREA_MGR, ACCT_MGR FROM MIS.ACC_BASE_DATA WHERE ACCT_MGR GT '' GROUP BY 1,2) B) Z
LEFT OUTER JOIN (
SELECT B.ACCT_MGR, A.PRODUCT, A.FEATURE,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_O,
 TOT_I-(ALTCHNL_I+COR_I+CS_I+D2D_I+MOBILITYDMD_I+NATIONALRET_I+OBTM_I+ONLINE_I+P2RCALLCEN_I+STORES_I+TE_I+WEB_I) OTH_I,
 TOT_O-(ALTCHNL_O+COR_O+CS_O+D2D_O+MOBILITYDMD_O+NATIONALRET_O+OBTM_O+ONLINE_O+P2RCALLCEN_O+STORES_O+TE_O+WEB_O) OTH_O,
 SUM(CASE WHEN A.DIRECTION='IN' THEN 1 ELSE 0 END) TOT_I,
 SUM(CASE WHEN A.DIRECTION='OUT' THEN 1 ELSE 0 END) TOT_O
FROM MIS.ACC_RAW A
 INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID,
 (SELECT MIN(CALENDAR_DATE) BOM, MAX(CALENDAR_DATE) EOM
  FROM SYS_CALENDAR.CALENDAR
  WHERE MONTH_OF_CALENDAR=(
  SELECT MONTH_OF_CALENDAR  FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)) C
WHERE A.FEATURE NOT IN ('000','BVSTAN')
AND A.START_DT BETWEEN C.BOM AND C.EOM
GROUP BY 1,2,3) Y ON Z.ACCT_MGR=Y.ACCT_MGR AND Z.PRODUCT=Y.PRODUCT AND Z.FEATURE=Y.FEATURE
) sums
GROUP	BY 1, ROLLUP (2,3)
ORDER	BY 1,2,3,4;

--6
SELECT  DISTINCT Z.PORTFOLIO, Z.PRODUCT,
CASE Z.FEATURE
 WHEN 'Expres' THEN 'Express'
 WHEN 'Hsia18' THEN 'Max18'
 WHEN 'BOT100' THEN 'VOIP 1000'
 WHEN 'BOT250' THEN 'VOIP 250'
 WHEN 'UNLMTD' THEN 'VOIP Unlimited'
 WHEN 'UFAM' THEN 'UFamily'
 WHEN 'U200la' THEN 'U200 Latino'
 WHEN 'Ubasic' THEN 'UBasic'
 WHEN 'Ucomp' THEN 'UComp'
 WHEN 'Udemo' THEN 'UDemo'
 ELSE Z.FEATURE
END FEATURE,
COALESCE(Y.ALTCHNL_I,0) ALTCHNL_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ALTCHNL_I/TOT_I AS DECIMAL(5,1))/10 END ALTCHNL_PCT, COALESCE(Y.ALTCHNL_O,0) ALTCHNL_OUT,
COALESCE(Y.COR_I,0) COR_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*COR_I/TOT_I AS DECIMAL(5,1))/10 END COR_PCT, COALESCE(Y.COR_O,0) COR_OUT,
COALESCE(Y.CS_I,0) CS_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*CS_I/TOT_I AS DECIMAL(5,1))/10 END CS_PCT, COALESCE(Y.CS_O,0) CS_OUT,
COALESCE(Y.D2D_I,0) D2D_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*D2D_I/TOT_I AS DECIMAL(5,1))/10 END D2D_PCT, COALESCE(Y.D2D_O,0) D2D_OUT,
COALESCE(Y.MOBILITYDMD_I,0) MOBILITYDMD_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*MOBILITYDMD_I/TOT_I AS DECIMAL(5,1))/10 END MOBILITYDMD_PCT, COALESCE(Y.MOBILITYDMD_O,0) MOBILITYDMD_OUT,
COALESCE(Y.NATIONALRET_I,0) NATIONALRET_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*NATIONALRET_I/TOT_I AS DECIMAL(5,1))/10 END NATIONALRET_PCT, COALESCE(Y.NATIONALRET_O,0) NATIONALRET_OUT,
COALESCE(Y.OBTM_I,0) OBTM_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*OBTM_I/TOT_I AS DECIMAL(5,1))/10 END OBTM_PCT, COALESCE(Y.OBTM_O,0) OBTM_OUT,
COALESCE(Y.ONLINE_I,0) ONLINE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ONLINE_I/TOT_I AS DECIMAL(5,1))/10 END ONLINE_PCT, COALESCE(Y.ONLINE_O,0) ONLINE_OUT,
COALESCE(Y.P2RCALLCEN_I,0) P2RCALLCEN_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*P2RCALLCEN_I/TOT_I AS DECIMAL(5,1))/10 END P2RCALLCEN_PCT, COALESCE(Y.P2RCALLCEN_O,0) P2RCALLCEN_OUT,
COALESCE(Y.STORES_I,0) STORES_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*STORES_I/TOT_I AS DECIMAL(5,1))/10 END STORES_PCT, COALESCE(Y.STORES_O,0) STORES_OUT,
COALESCE(Y.TE_I,0) TE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*TE_I/TOT_I AS DECIMAL(5,1))/10 END TE_PCT, COALESCE(Y.TE_O,0) TE_OUT,
COALESCE(Y.WEB_I,0) WEB_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*WEB_I/TOT_I AS DECIMAL(5,1))/10 END WEB_PCT, COALESCE(Y.WEB_O,0) WEB_OUT,
COALESCE(TOT_I,0)  + COALESCE(TOT_O,0)  TOT,
'PORTFOLIO' ROLLUP_TYPE
FROM (
SELECT B.PORTFOLIO, A.PRODUCT, A.FEATURE 
FROM (SELECT PRODUCT, FEATURE FROM MIS.ACC_RAW WHERE FEATURE NOT IN ('000','BVSTAN') GROUP BY 1,2) A,
     (SELECT AREA_MGR,  PORTFOLIO FROM MIS.ACC_BASE_DATA WHERE TRAC_ID GT PORTFOLIO GROUP BY 1,2) B) Z
LEFT OUTER JOIN (
SELECT B.PORTFOLIO, A.PRODUCT, A.FEATURE,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_O,
 TOT_I-(ALTCHNL_I+COR_I+CS_I+D2D_I+MOBILITYDMD_I+NATIONALRET_I+OBTM_I+ONLINE_I+P2RCALLCEN_I+STORES_I+TE_I+WEB_I) OTH_I,
 TOT_O-(ALTCHNL_O+COR_O+CS_O+D2D_O+MOBILITYDMD_O+NATIONALRET_O+OBTM_O+ONLINE_O+P2RCALLCEN_O+STORES_O+TE_O+WEB_O) OTH_O,
 SUM(CASE WHEN A.DIRECTION='IN' THEN 1 ELSE 0 END) TOT_I,
 SUM(CASE WHEN A.DIRECTION='OUT' THEN 1 ELSE 0 END) TOT_O
FROM MIS.ACC_RAW A
 INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID,
 (SELECT MIN(CALENDAR_DATE) BOM, MAX(CALENDAR_DATE) EOM
  FROM SYS_CALENDAR.CALENDAR
  WHERE MONTH_OF_CALENDAR=(
  SELECT MONTH_OF_CALENDAR  FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)) C
WHERE A.FEATURE NOT IN ('000','BVSTAN')
AND A.START_DT BETWEEN C.BOM AND C.EOM
GROUP BY 1,2,3) Y ON Z.PORTFOLIO=Y.PORTFOLIO AND Z.PRODUCT=Y.PRODUCT AND Z.FEATURE=Y.FEATURE
WHERE TOT>0 
ORDER BY 1,2,3,4;
--7
SELECT DISTINCT Z.TRAC_ID, Z.PRODUCT,
CASE Z.FEATURE
 WHEN 'Expres' THEN 'Express'
 WHEN 'Hsia18' THEN 'Max18'
 WHEN 'BOT100' THEN 'VOIP 1000'
 WHEN 'BOT250' THEN 'VOIP 250'
 WHEN 'UNLMTD' THEN 'VOIP Unlimited'
 WHEN 'UFAM' THEN 'UFamily'
 WHEN 'U200la' THEN 'U200 Latino'
 WHEN 'Ubasic' THEN 'UBasic'
 WHEN 'Ucomp' THEN 'UComp'
 WHEN 'Udemo' THEN 'UDemo'
 ELSE Z.FEATURE
END FEATURE,
COALESCE(Y.ALTCHNL_I,0) ALTCHNL_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ALTCHNL_I/TOT_I AS DECIMAL(5,1))/10 END ALTCHNL_PCT, COALESCE(Y.ALTCHNL_O,0) ALTCHNL_OUT,
COALESCE(Y.COR_I,0) COR_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*COR_I/TOT_I AS DECIMAL(5,1))/10 END COR_PCT, COALESCE(Y.COR_O,0) COR_OUT,
COALESCE(Y.CS_I,0) CS_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*CS_I/TOT_I AS DECIMAL(5,1))/10 END CS_PCT, COALESCE(Y.CS_O,0) CS_OUT,
COALESCE(Y.D2D_I,0) D2D_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*D2D_I/TOT_I AS DECIMAL(5,1))/10 END D2D_PCT, COALESCE(Y.D2D_O,0) D2D_OUT,
COALESCE(Y.MOBILITYDMD_I,0) MOBILITYDMD_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*MOBILITYDMD_I/TOT_I AS DECIMAL(5,1))/10 END MOBILITYDMD_PCT, COALESCE(Y.MOBILITYDMD_O,0) MOBILITYDMD_OUT,
COALESCE(Y.NATIONALRET_I,0) NATIONALRET_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*NATIONALRET_I/TOT_I AS DECIMAL(5,1))/10 END NATIONALRET_PCT, COALESCE(Y.NATIONALRET_O,0) NATIONALRET_OUT,
COALESCE(Y.OBTM_I,0) OBTM_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*OBTM_I/TOT_I AS DECIMAL(5,1))/10 END OBTM_PCT, COALESCE(Y.OBTM_O,0) OBTM_OUT,
COALESCE(Y.ONLINE_I,0) ONLINE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*ONLINE_I/TOT_I AS DECIMAL(5,1))/10 END ONLINE_PCT, COALESCE(Y.ONLINE_O,0) ONLINE_OUT,
COALESCE(Y.P2RCALLCEN_I,0) P2RCALLCEN_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*P2RCALLCEN_I/TOT_I AS DECIMAL(5,1))/10 END P2RCALLCEN_PCT, COALESCE(Y.P2RCALLCEN_O,0) P2RCALLCEN_OUT,
COALESCE(Y.STORES_I,0) STORES_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*STORES_I/TOT_I AS DECIMAL(5,1))/10 END STORES_PCT, COALESCE(Y.STORES_O,0) STORES_OUT,
COALESCE(Y.TE_I,0) TE_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*TE_I/TOT_I AS DECIMAL(5,1))/10 END TE_PCT, COALESCE(Y.TE_O,0) TE_OUT,
COALESCE(Y.WEB_I,0) WEB_IN, CASE WHEN TOT_I=0 THEN 0 ELSE CAST(1000*WEB_I/TOT_I AS DECIMAL(5,1))/10 END WEB_PCT, COALESCE(Y.WEB_O,0) WEB_OUT,
COALESCE(TOT_I,0)  + COALESCE(TOT_O,0)  TOT,
'TRAC_ID' ROLLUP_TYPE
FROM (
SELECT B.TRAC_ID, A.PRODUCT, A.FEATURE 
FROM (SELECT PRODUCT, FEATURE FROM MIS.ACC_RAW WHERE FEATURE NOT IN ('000','BVSTAN') GROUP BY 1,2) A,
     (SELECT AREA_MGR, TRAC_ID FROM MIS.ACC_BASE_DATA WHERE TRAC_ID GT '' GROUP BY 1,2) B) Z
LEFT OUTER JOIN (
SELECT A.TRAC_ID, A.PRODUCT, A.FEATURE,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ALT CHNL' THEN 1 ELSE 0 END) ALTCHNL_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='COR' THEN 1 ELSE 0 END) COR_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='CS' THEN 1 ELSE 0 END) CS_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='D2D' THEN 1 ELSE 0 END) D2D_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='MOBILITY DMD' THEN 1 ELSE 0 END) MOBILITYDMD_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='NATIONAL RET' THEN 1 ELSE 0 END) NATIONALRET_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='OBTM' THEN 1 ELSE 0 END) OBTM_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='ONLINE' THEN 1 ELSE 0 END) ONLINE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='P2R CALL CEN' THEN 1 ELSE 0 END) P2RCALLCEN_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='STORES' THEN 1 ELSE 0 END) STORES_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='TE' THEN 1 ELSE 0 END) TE_O,
 SUM(CASE WHEN A.DIRECTION='IN' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_I,
 SUM(CASE WHEN A.DIRECTION='OUT' AND A.SALES_CHANNEL_CD='WEB' THEN 1 ELSE 0 END) WEB_O,
 TOT_I-(ALTCHNL_I+COR_I+CS_I+D2D_I+MOBILITYDMD_I+NATIONALRET_I+OBTM_I+ONLINE_I+P2RCALLCEN_I+STORES_I+TE_I+WEB_I) OTH_I,
 TOT_O-(ALTCHNL_O+COR_O+CS_O+D2D_O+MOBILITYDMD_O+NATIONALRET_O+OBTM_O+ONLINE_O+P2RCALLCEN_O+STORES_O+TE_O+WEB_O) OTH_O,
 SUM(CASE WHEN A.DIRECTION='IN' THEN 1 ELSE 0 END) TOT_I,
 SUM(CASE WHEN A.DIRECTION='OUT' THEN 1 ELSE 0 END) TOT_O
FROM MIS.ACC_RAW A
 INNER JOIN MIS.ACC_BASE_DATA B ON A.TRAC_ID=B.TRAC_ID,
 (SELECT MIN(CALENDAR_DATE) BOM, MAX(CALENDAR_DATE) EOM
  FROM SYS_CALENDAR.CALENDAR
  WHERE MONTH_OF_CALENDAR=(
  SELECT MONTH_OF_CALENDAR  FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=CURRENT_DATE)) C
WHERE A.FEATURE NOT IN ('000','BVSTAN')
AND A.START_DT BETWEEN C.BOM AND C.EOM
GROUP BY 1,2,3) Y ON Z.TRAC_ID=Y.TRAC_ID AND Z.PRODUCT=Y.PRODUCT AND Z.FEATURE=Y.FEATURE
WHERE TOT>0
ORDER BY 1,2,3,4;